package cn.gxa.edu_adm.dao.impl;

import cn.gxa.edu_adm.bean.Course;
import cn.gxa.edu_adm.dao.CourseDao;
import cn.gxa.edu_adm.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//  couId  couName   couContext  couScore  couTypeId  couCreateTime couUpdateTime couState:状态 0可用,1不可用
public class CourseDaoImpl implements CourseDao {
    @Override
    public List<Course> findByManyWhere(Integer page, Integer limit, String name, Integer courseType, Double startSocre, Double endSocre) {
        //参数集合
        List<Object>  params=new ArrayList<>();
        //String 面试题 怎么优化sql  1.不能写1=1 2.尽量查询不用 *写上明确的查询字段
        StringBuffer  sql=new StringBuffer("select *  from Course  where 1=1");  //sql动态 拼接
        //name  有值 where  name=?
        if(name!=null){
            sql.append(" and  couName like ?");
            params.add("%"+name+"%");
        }
        // courseType  where  courseType=?
        if(courseType!=null){
            sql.append(" and  couTypeId=?");
            params.add(courseType);
        }
        if(startSocre!=null){
            sql.append(" and  couScore>=?");
            params.add(startSocre);
        }
        if(endSocre!=null){
            sql.append(" and  couScore<=?");
            params.add(endSocre);
        }
        //(page-1)*limit
        sql.append(" order by couUpdateTime desc limit "+(page-1)*limit+","+limit);
        System.out.println("sql:"+sql);
        //查询数据库
        QueryRunner   queryRunner  =new QueryRunner(DruidUtils.getDruidDataSource());
        try {
            return   queryRunner.query(sql.toString(),new BeanListHandler<Course>(Course.class),params.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Long findByManyWhereCount( String name, Integer courseType, Double startSocre, Double endSocre) {
        //参数集合
            List<Object>  params=new ArrayList<>();
            //String 面试题 怎么优化sql  1.不能写1=1 2.尽量查询不用 *写上明确的查询字段
            StringBuffer  sql=new StringBuffer("select count(couId)  from Course  where 1=1");  //sql动态 拼接
            //name  有值 where  name=?
            if(name!=null){
                sql.append(" and  couName like ?");
                params.add("%"+name+"%");
            }
            // courseType  where  courseType=?
            if(courseType!=null){
                sql.append(" and  couTypeId=?");
                params.add(courseType);
            }
            if(startSocre!=null){
                sql.append(" and  couScore>=?");
            params.add(startSocre);
        }
        if(endSocre!=null){
            sql.append(" and  couScore<=?");
            params.add(endSocre);
        }

        System.out.println("sql:"+sql);
        //查询数据库
        QueryRunner   queryRunner  =new QueryRunner(DruidUtils.getDruidDataSource());
        try {
            Object  count=queryRunner.query(sql.toString(),new ArrayHandler(),params.toArray())[0];
            return   Long.parseLong(count.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int deleteById(Integer id) {
        QueryRunner  queryRunner=new QueryRunner(DruidUtils.getDruidDataSource());
        String  sql="delete  from  Course where couId=?";
        try {
           return   queryRunner.update(sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int deleteByIds(String[] ids) {
        return 0;
    }

    @Override
    public int update(Course course) {
        QueryRunner  queryRunner=new QueryRunner(DruidUtils.getDruidDataSource());
        String  sql="update  Course set couName=?,couContext=?,couScore=?,couTypeId=?,couUpdateTime=now() " +
                "where  couId=?";
        int  rows=0;
        try {
             rows=  queryRunner.update(sql,course.getCouName(),
                    course.getCouContext(),course.getCouScore(),course.getCouTypeId(),course.getCouId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }

    @Override
    public int insert(Course course) {
        QueryRunner  queryRunner=new QueryRunner(DruidUtils.getDruidDataSource());
        String  sql="insert into Course(couName,couContext,couScore,couTypeId) value(?,?,?,?)";
        try {
            return   queryRunner.update(sql,course.getCouName(),course.getCouContext(),course.getCouScore(),course.getCouTypeId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }


}
